package com.fly.estore.dao.Impl;

import com.fly.estore.dao.BaseDAO;
import com.fly.estore.dao.StockDetailDAO;
import com.fly.estore.domain.ProductInfo;
import com.fly.estore.domain.ProductSKU;
import com.fly.estore.domain.StockDetail;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Fly on 2016/4/26.
 */
@Repository("StockDetailDAO")
public class StockDetailDAOImpl extends BaseDAO<StockDetail> implements StockDetailDAO {

    public int addStockDetail(final StockDetail stockDetail) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("insert into stock_detail(stock_detail_id, stock_id, product_id, product_sku_id, amount, purchase_price, create_id, create_time)")
                .append("values(?, ?, ?, ?, ?, ?, ?, ?)");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, stockDetail.getStockDetailId());
                ps.setString(i++, stockDetail.getStockId());
                ps.setString(i++, stockDetail.getProduct().getProductId());
                ps.setString(i++, stockDetail.getProductSKU().getProductSkuId());
                ps.setInt(i++, stockDetail.getAmount());
                ps.setFloat(i++, stockDetail.getPurchasePrice());
                ps.setString(i++, stockDetail.getCreateId());
                ps.setString(i++, stockDetail.getCreateTime());
            }
        });
        return update;
    }

    public int updateStockDetail(final StockDetail stockDetail) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("update stock_detail set")
                .append(" product_id = ?,")
                .append(" product_sku_id = ?,")
                .append(" amount = ?,")
                .append(" purchase_price = ?,")
                .append(" last_update_id = ?,")
                .append(" last_update_time = ?")
                .append(" where stock_detail_id = ?");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, stockDetail.getProduct().getProductId());
                ps.setString(i++, stockDetail.getProductSKU().getProductSkuId());
                ps.setInt(i++, stockDetail.getAmount());
                ps.setFloat(i++, stockDetail.getPurchasePrice());
                ps.setString(i++, stockDetail.getLastUpdateId());
                ps.setString(i++, stockDetail.getLastUpdateTime());
                ps.setString(i++, stockDetail.getStockDetailId());
            }
        });
        return update;
    }

    public int deleteById(final String stockDetailId) {
        int update = 0;
        String sql = "delete from stock_detail where stock_detail_id = ?";
        update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, stockDetailId);
            }
        });
        return update;
    }

    public int deleteByStockId(final String stockId){
        int update = 0;
        String sql = "delete from stock_detail where stock_id = ?";
        update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, stockId);
            }
        });
        return update;
    }

    public StockDetail getById(String stockDetailId) {
        StockDetail stockDetail = new StockDetail();
        StringBuilder sql = new StringBuilder();
        sql.append("select sd.stock_detail_id, sd.amount, sd.purchase_price, sd.create_id, sd.create_time, sd.last_update_id, sd.last_update_time,")
                .append("p.product_id, p.product_code, p.product_name,")
                .append("ps.product_sku_id, ps.sku_code, ps.sku_name")
                .append(" from stock_detail sd ")
                .append(" left join product_info p on p.product_id = sd.product_id")
                .append(" left join product_sku ps on ps.product_sku_id = sd.product_sku_id")
                .append(" where stock_detail_id = ?");
        List<StockDetail> resultList = getJdbcTemplate().query(sql.toString(), new StockDetailRowMapper(), new Object[]{stockDetailId});
        if (!CollectionUtils.isEmpty(resultList)){
            stockDetail = resultList.get(0);
        }
        return stockDetail;
    }

    public List<StockDetail> getByStockId(String stockId) {
        List<StockDetail> detailList = new ArrayList<StockDetail>();
        StringBuilder sql = new StringBuilder();
        sql.append("select sd.stock_detail_id, sd.amount, sd.purchase_price, sd.create_id, sd.create_time, sd.last_update_id, sd.last_update_time,")
                .append("p.product_id, p.product_code, p.product_name,")
                .append("ps.product_sku_id, ps.sku_code, ps.sku_name")
                .append(" from stock_detail sd ")
                .append(" left join product_info p on p.product_id = sd.product_id")
                .append(" left join product_sku ps on ps.product_sku_id = sd.product_sku_id")
                .append(" where stock_id = ?");
        detailList = getJdbcTemplate().query(sql.toString(), new StockDetailRowMapper(), new Object[]{stockId});
        return detailList;
    }

    public List<StockDetail> getList() {
        List<StockDetail> detailList = new ArrayList<StockDetail>();
        StringBuilder sql = new StringBuilder();
        sql.append("select sd.stock_detail_id, sd.amount, sd.purchase_price, sd.create_id, sd.create_time, sd.last_update_id, sd.last_update_time,")
                .append("p.product_id, p.product_code, p.product_name,")
                .append("ps.product_sku_id, ps.sku_code, ps.sku_name")
                .append(" from stock_detail sd ")
                .append(" left join product_info p on p.product_id = sd.product_id")
                .append(" left join product_sku ps on ps.product_sku_id = sd.product_sku_id")
                .append(" order by sd.create_time desc");
        detailList = getJdbcTemplate().query(sql.toString(), new StockDetailRowMapper());
        return detailList;
    }

    public class StockDetailRowMapper implements RowMapper<StockDetail> {
        public StockDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
            StockDetail stockDetail = new StockDetail();
            stockDetail.setStockDetailId(rs.getString("stock_detail_id"));
            stockDetail.setAmount(rs.getInt("amount"));
            stockDetail.setPurchasePrice(rs.getFloat("purchase_price"));
            stockDetail.setCreateId(rs.getString("create_id"));
            stockDetail.setCreateTime(rs.getString("create_time"));
            stockDetail.setLastUpdateId(rs.getString("last_update_id"));
            stockDetail.setLastUpdateTime(rs.getString("last_update_time"));
            ProductInfo product = new ProductInfo();
            product.setProductId(rs.getString("product_id"));
            product.setProductName(rs.getString("product_name"));
            product.setProductCode(rs.getString("product_code"));
            stockDetail.setProduct(product);
            ProductSKU productSKU = new ProductSKU();
            productSKU.setProductSkuId(rs.getString("product_sku_id"));
            productSKU.setSkuCode(rs.getString("sku_code"));
            productSKU.setSkuName(rs.getString("sku_name"));
            stockDetail.setProductSKU(productSKU);
            return stockDetail;
        }
    }
}
